Product Code Database
Oracle Database provides information about all of the tables, views, columns, and Stored procedure in a database. This information about information is known as metadata. It is stored in two locations: data dictionary tables (accessed via built-in views) and a metadata registry.
Other relational database management systems support an ANSI-standard equivalent called information schema.
The main built-in views accessing Oracle RDBMS data dictionary tables are few, and are as follows:
In addition there are equivalent views prefixed "USER_" which show only the objects owned by the current user (i.e. a more restricted view of metadata) and prefixed "DBA_" which show all objects in the database (i.e. an unrestricted global view of metadata for the database instance). Naturally the access to "DBA_" metadata views requires specific privileges.
SELECT Owner AS Schema_Name, Table_Name
FROM All_Tables
WHERE Table_Name LIKE '%PATTERN%'
ORDER BY Owner, Table_Name;
SELECT Owner AS Schema_Name, Table_Name, Column_Name
FROM All_Tab_Columns
WHERE Column_Name LIKE '%PATTERN%'
ORDER BY 1,2,3;
COLUMN DUMMY NOPRINT
COMPUTE SUM OF NUM_ROWS ON DUMMY
BREAK ON DUMMY
SELECT
NULL DUMMY,
T.TABLE_NAME,
C.COLUMN_NAME,
T.NUM_ROWS
FROM
ALL_TABLES T,
ALL_TAB_COLUMNS C
WHERE
T.TABLE_NAME = C.TABLE_NAME
AND C.COLUMN_NAME LIKE '%PATTERN%'
AND T.OWNER = C.OWNER
ORDER BY T.TABLE_NAME;
column_name,
decode(c.DATA_TYPE,
'VARCHAR2',
c.DATA_TYPE || '(' || c.DATA_LENGTH || ')',
'NUMBER',
DECODE(c.data_precision,
NULL,
c.DATA_TYPE,
0,
c.DATA_TYPE,
c.DATA_TYPE || '(' || c.data_precision || DECODE(c.data_scale,
NULL,
')',
0,
')' ,
', ' || c.data_scale || ')')),
c.DATA_TYPE) data_type
FROM cols c, obj o
WHERE c.TABLE_NAME = o.object_name
AND o.object_type = 'VIEW'
AND c.table_name LIKE '%PATTERN%'
ORDER BY c.table_name, c.column_id;
Just add the following after a LIKE statement:
ESCAPE '_'
And then each literal underscore should be a double underscore: __
Example
LIKE '%__G' ESCAPE '_'
|
|
